package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataHelper;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.data.ParameterSqlModel;
import com.szholly.pro.watergas_server.common.data.SqlUtil;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

import net.sf.json.JSONArray;

@Service
public class QCBaseInfoManagementService {
	protected Logger logger = LoggerFactory.getLogger(getClass());
	
	/**
	 * 获取监测手段列表数据
	 * @param resq
	 * @param res
	 * @throws Exception
	 */	
	public String getMonitorMethodList() throws Exception{
	    String sql="select t.s_domaincaption as MonitorMethod,to_char(I_ORDER) as MonitorMethodCode"
	    		+" from DATA_DEFULATDOMAIN t where t.s_domainname = '监测手段'";		
		return DataUtil.getPageSelectJson(sql, null);
	}
	/**
	 * 获取质控类型列表数据
	 * @param resq
	 * @param res
	 * @throws Exception
	 */	
	public String getQualityControlTypeList() throws Exception{
	    String sql="select t.s_domaincode,t.s_domaincaption as QCTypeName from DATA_DEFULATDOMAIN t"
	    		+" where t.s_domainname = '质控类型'";
		return DataUtil.getPageSelectJson(sql, null);
	}
	
	/**
	 * 获取质控类型列表数据(无普通样,用于汇总评价)
	 * @param resq
	 * @param res
	 * @throws Exception
	 */	
	public List<RowBase> getSumEvaQualityControlTypeList(Map<String,String> params) throws Exception{
	    String sql="select t.s_domaincode,t.s_domaincaption as QCTypeName from DATA_DEFULATDOMAIN t"
	    		+" where t.s_domainname = '质控类型' and t.s_domaincode != 6";
	    if(StringUtil.IsNotNullOrEmpty(params.get("QCType"))){
	    	sql+=" and t.s_domaincode in("+ParamPartition(params.get("QCType"))+")";
	    }
	    List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> SampleList = DataUtil.getSelectRows(sql, listParam);
		return SampleList;
	}
	
	/**
	 * 获取监测类型列表数据
	 * @param resq
	 * @param res
	 * @throws Exception
	 */	
	public List<RowBase> getMonitorTypeList(Map<String,String> params) throws Exception{
	    String sql="select t.s_domaincaption as MonitorType,t.s_domaincode as MonitorTypeCode from DATA_DEFULATDOMAIN t where t.s_domainname = '质控监测类型'";
	    if(StringUtil.IsNotNullOrEmpty(params.get("MONITORINGTYPE"))){
	    	sql+=" and t.i_order in ("+ParamPartition(params.get("MONITORINGTYPE"))+")";
	    }		
	    sql+= " order by case when t.s_domaincode='2' then  20 when t.s_domaincode='4' then 21 else 1 end,t.i_order asc";
	    List<GlobalParameter> listParam = new ArrayList<>();
		List<RowBase> MonitorList = DataUtil.getSelectRows(sql, listParam);
		return MonitorList;
	}
	/**
	 * 获取质控人员身份类型列表数据
	 * @param resq
	 * @param res
	 * @throws Exception
	 */	
	public String getQCPersonTypeList() throws Exception{
	    String sql="select t.s_domaincaption as QCPersonTypeName,S_DOMAINCODE as QCPersonTypeCode  from DATA_DEFULATDOMAIN t where t.s_domainname = '质控人员身份'";
		return DataUtil.getPageSelectJson(sql, null);
	}
	
	/**
	 * 获取公司监测类型信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public RowBase getCompanyMonitorTypeData(String COMPANYCODE) throws Exception{
			String sql = "select t1.monitortypecode,t1.monitortype from qc_companydetailinfo t"
					+ " left join (select t.s_domaincode as MonitorTypeCode,t.s_domaincaption as MonitorType from DATA_DEFULATDOMAIN t"
					+ " where t.s_domainname = '质控监测类型') t1"
					+ " on t.monitoringtypecode = t1.monitortypecode"				
					+ " where t.companycode = '"+COMPANYCODE+"'";
			RowBase result = new RowBase();;
			List<RowBase> childList = DataUtil.getSelectRows(sql,null);
			result.put("MonitorTypeList", childList);
			return result;
	}
	
	/**
	 * 获取公司监测参数信息  
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public RowBase getCompanyMonitorParamsData(String COMPANYCODE) throws Exception{
			String sql = "select PARAMGUID as GUID from qc_companyparaminfo t"			
					+ " where t.companycode = '"+COMPANYCODE+"'";
			RowBase result = new RowBase();
			List<RowBase> childList = DataUtil.getSelectRows(sql,null);
			result.put("MonitorParamsList", childList);
			return result;
	}
	
	/**
	 * 新增点位信息 
	 * 张文成
	 * 2016年12月09日
	 * @param res
	 */
	public boolean createPoint(Map<String, String> parm) throws Exception{
		try{    
				DataUtil.beginTransaction();
				String sql=" insert into QC_POINTINFO(POINTCODE,COMPANYCODE,POINTTYPE) "
						+ " values('"+parm.get("POINTCODE")+"','"+parm.get("COMPANYCODE")+"','"+parm.get("POINTTYPECODE")+"')";
         
				DataUtil.ExecuteUpdateSql(sql, null);
				sql=" insert into MONITOR_STATION(NAME,CODE,SAMPLE_POSITION,X,Y,STATION_CATEGORY,BUILD_STATUS,photo) "
							+ " values('"+parm.get("POINTNAME")+"','"+parm.get("POINTCODE")+"','"+parm.get("SAMPLEPOSITION")+"','"+parm.get("LONGITUDE")+"'"
									+ ",'"+parm.get("LATITUDE")+"','8','1','"+parm.get("PHOTO")+"')";
				DataUtil.ExecuteUpdateSql(sql, null);	
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 编辑点位信息 
	 * 张文成
	 * 2016年12月09日
	 * @param res
	 */
	public boolean editPoint(Map<String, String> parm) throws Exception{
		try{
				DataUtil.beginTransaction();
				String sql=" update QC_POINTINFO set COMPANYCODE='"+parm.get("COMPANYCODE")+"',POINTTYPE='"+parm.get("POINTTYPECODE")+"'"
						+ " where POINTCODE='"+parm.get("POINTCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				
				sql=" update MONITOR_STATION set NAME='"+parm.get("POINTNAME")+"',SAMPLE_POSITION='"+parm.get("SAMPLEPOSITION")+"'"
						+ " ,X='"+parm.get("LONGITUDE")+"',Y='"+parm.get("LATITUDE")+"' "
						+ " ,STATION_CATEGORY='8',PHOTO='"+parm.get("PHOTO")+"' where CODE='"+parm.get("POINTCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 删除点位信息 
	 * 张文成
	 * 2016年12月09日
	 * @param res
	 */
	public boolean deletePoint(Map<String, String> parm) throws Exception{
		try{
				DataUtil.beginTransaction();
				String sql=" delete QC_POINTINFO where POINTCODE = '"+parm.get("POINTCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				sql=" delete MONITOR_STATION t where t.CODE = '"+parm.get("POINTCODE")+"'";  
				DataUtil.ExecuteUpdateSql(sql, null);
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 获取所有点位信息 
	 * 张文成
	 * 2016年12月09日
	 * @param res
	 * @throws Exception 
	 */
	public String getPointData(Map<String, String> parm) throws Exception {
			String sql="select t.pointcode AS POINTCODE,t.companycode,t.pointtype as POINTTYPECODE,t2.companyname AS COMPANYNAME ,t1.name as POINTNAME,t1.sample_position as SAMPLEPOSITION,"
					+" t4.s_domaincaption AS POINTTYPE ,t1.photo,t1.temp_photo,t1.X as LONGITUDE,t1.Y as LATITUDE from qc_pointinfo t"
					+" left join BC_SITE_INFO t1 on t.pointcode = t1.code left join qc_companyinfo t2 on t.companycode = t2.companycode"
					+" left join (select * from DATA_DEFULATDOMAIN t where t.s_domainname ='质控监测类型'） t4 on t.pointtype = t4.s_domaincode where 1=1";
			if(StringUtil.IsNotNullOrEmpty(parm.get("POINTCODE"))){
				sql+=" and t.POINTCODE ='"+parm.get("POINTCODE")+"'";
			}			
			sql+= " order by t.pointcode asc";
			return DataUtil.getPageSelectJson(sql, null);
	}
	
	
	/**
	 * 新增公司信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public boolean createCompany(Map<String, String> parm,String[] arrMonitorTypeId,String[] arrParamGUId) throws Exception{
		try{
				DataUtil.beginTransaction();
				String GUID =  UUID.randomUUID().toString();
				String sql=" insert into  QC_COMPANYINFO(GUID,COMPANYNAME,COMPANYTYPE,COMPANYCODE,SAMPLENUMBERRULE) "
						+ "values('"+GUID+"','"+parm.get("COMPANYNAME")+"','"+parm.get("COMPANYTYPECODE")+"',"
						+ "'"+parm.get("COMPANYCODE")+"','"+parm.get("SAMPLENUMBERRULE")+"')";

				DataUtil.ExecuteUpdateSql(sql, null);
				//监测类型入库
				sql=" delete QC_COMPANYDETAILINFO t where t.companycode = '"+parm.get("COMPANYCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				for(int i=0;i<arrMonitorTypeId.length;i++)
				{   
					String detailInfoGUID =  UUID.randomUUID().toString();
					sql=" insert into QC_COMPANYDETAILINFO(GUID,COMPANYCODE,MONITORINGTYPECODE) "
							+ "values('"+detailInfoGUID+"','"+parm.get("COMPANYCODE")+"','"+arrMonitorTypeId[i]+"')";
					DataUtil.ExecuteUpdateSql(sql, null);	
				}
				
				//监测参数入库
				sql=" delete QC_COMPANYPARAMINFO t where t.companycode = '"+parm.get("COMPANYCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				for(int i=0;i<arrParamGUId.length;i++)
				{   
					String paramInfoGUID =  UUID.randomUUID().toString();
					sql=" insert into QC_COMPANYPARAMINFO(GUID,COMPANYCODE,PARAMGUID) "
							+ "values('"+paramInfoGUID+"','"+parm.get("COMPANYCODE")+"','"+arrParamGUId[i]+"')";
					DataUtil.ExecuteUpdateSql(sql, null);	
				}
				
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 编辑公司信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public boolean editCompany(Map<String, String> parm,String[] arrMonitorTypeId,String[] arrParamGUId) throws Exception{
		try{
				DataUtil.beginTransaction();
				String sql=" update QC_COMPANYINFO set COMPANYNAME='"+parm.get("COMPANYNAME")+"',COMPANYTYPE='"+parm.get("COMPANYTYPECODE")+"'"
						+ ",COMPANYCODE='"+parm.get("COMPANYCODE")+"',SAMPLENUMBERRULE='"+parm.get("SAMPLENUMBERRULE")+"' where GUID='"+parm.get("GUID")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);

				//监测类型入库
				sql=" delete QC_COMPANYDETAILINFO t where t.companycode = '"+parm.get("COMPANYCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				for(int i=0;i<arrMonitorTypeId.length;i++)
				{   
					String detailInfoGUID =  UUID.randomUUID().toString();
					sql=" insert into QC_COMPANYDETAILINFO(GUID,COMPANYCODE,MONITORINGTYPECODE) "
							+ "values('"+detailInfoGUID+"','"+parm.get("COMPANYCODE")+"','"+arrMonitorTypeId[i]+"')";
					DataUtil.ExecuteUpdateSql(sql, null);	
				}
				
				//监测参数入库
				sql=" delete QC_COMPANYPARAMINFO t where t.companycode = '"+parm.get("COMPANYCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				for(int i=0;i<arrParamGUId.length;i++)
				{   
					String paramInfoGUID =  UUID.randomUUID().toString();
					sql=" insert into QC_COMPANYPARAMINFO(GUID,COMPANYCODE,PARAMGUID) "
							+ "values('"+paramInfoGUID+"','"+parm.get("COMPANYCODE")+"','"+arrParamGUId[i]+"')";
					DataUtil.ExecuteUpdateSql(sql, null);	
				}
				
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 删除公司信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public boolean deleteCompany(Map<String, String> parm) throws Exception{
		try{
				DataUtil.beginTransaction();
				String sql=" delete QC_COMPANYINFO where GUID = '"+parm.get("GUID")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				sql=" delete QC_COMPANYDETAILINFO t where t.companycode = '"+parm.get("COMPANYCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				sql=" delete QC_COMPANYPARAMINFO t where t.companycode = '"+parm.get("COMPANYCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 获取公司信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 * @throws Exception 
	 */
	public String getCompanyData(Map<String, String> parm) throws Exception{
			String sql="select t.guid,t.companyname,t.companycode,t.samplenumberrule,t1.s_domaincaption as COMPANYTYPENAME,t.companytype as companytypeCode from QC_COMPANYINFO t "
					+ " left join DATA_DEFULATDOMAIN t1 "
					+ " on t.companytype = to_char(t1.i_order) where  t1.s_domainname = '监测手段' ";
			if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){
				sql+=" and t.monitoringtype in("+ParamPartition(parm.get("MONITORINGTYPE"))+")";
			}
			if(StringUtil.IsNotNullOrEmpty(parm.get("MonitorMethod"))){
				sql+=" and t.COMPANYTYPE in("+ParamPartition(parm.get("MonitorMethod"))+")";
			}	
			if(StringUtil.IsNotNullOrEmpty(parm.get("CompanyCode"))){
				sql+=" and t.companycode in ("+ParamPartition(parm.get("CompanyCode"))+")";
			}
				sql+= " order by companycode asc";
				
			return DataUtil.getPageSelectJson(sql, null);

	}
	/**
	 * 根据监测类型获取公司信息(去重后的结果)
	 * 张文成
	 * 2016年12月16日
	 * @param res
	 * @throws Exception 
	 */
	public String getDistinctCompanyData(Map<String, String> parm) throws Exception{
			String sql="select distinct  t.companyname,t.companycode  from  QC_COMPANYINFO t "
					+ " left join qc_companydetailinfo t1 on t.companycode = t1.companycode where 1=1";
			if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORINGTYPE"))){
				sql+=" and t1.monitoringtypecode in("+ParamPartition(parm.get("MONITORINGTYPE"))+")";
			}
			if(StringUtil.IsNotNullOrEmpty(parm.get("COMPANYTYPECODE"))){
				sql+=" and t.COMPANYTYPE in("+ParamPartition(parm.get("COMPANYTYPECODE"))+")";
			}	
				sql+= " order by companycode asc";
			return DataUtil.getPageSelectJson(sql, null);
	}
	
	
	/**
	 * 新增公司人员信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public boolean addCompanyPerson(Map<String, String> parm) throws Exception{
		try{
			String GUID =  UUID.randomUUID().toString();
			String sql=" insert into QC_COMPANYPERSONINFO(GUID,PERSONNAME,PERSONCODE,PERSONTYPE,COMPANYCODE) "
						+ "values('"+GUID+"','"+parm.get("PERSONNAME")+"','"+parm.get("PERSONCODE")+"',"
						+ "'"+parm.get("PERSONTYPE")+"','"+parm.get("COMPANYCODE")+"')";
			DataUtil.ExecuteUpdateSql(sql, null);	
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	/**
	 * 删除公司人员信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public boolean deleteCompanyPerson(Map<String, String> parm) throws Exception{
		try{
			String sql=" delete QC_COMPANYPERSONINFO where GUID = '"+parm.get("GUID")+"'";
			DataUtil.ExecuteUpdateSql(sql, null);
			return true;
		} catch (Exception e) {
			return false;
		}
	}
	/**
	 * 编辑公司人员信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public boolean editCompanyPerson(Map<String, String> parm) throws Exception{
		try{
			String sql=" update QC_COMPANYPERSONINFO set PERSONNAME='"+parm.get("PERSONNAME")+"',PERSONCODE='"+parm.get("PERSONCODE")+"'"
					+ ",PERSONTYPE='"+parm.get("PERSONTYPE")+"',COMPANYCODE='"+parm.get("COMPANYCODE")+"' where GUID='"+parm.get("GUID")+"'";
			DataUtil.ExecuteUpdateSql(sql, null);
			return true;
		} catch (Exception e) {
			return false;
		}

	}
	/**
	 * 获取公司人员信息 
	 * 张文成
	 * 2016年11月28日
	 * @param res
	 */
	public String getCompanyPersonData() throws Exception{
		String sql="select t.*,t1.companyname,t2.s_domaincaption as PERSONTYPENAME from QC_COMPANYPERSONINFO t left join QC_COMPANYINFO t1 on t.companycode = t1.companycode "
				+ " left join  DATA_DEFULATDOMAIN t2 on t.persontype = t2.s_domaincode where t2.s_domainname = '质控人员身份' "
                + " order by t.COMPANYCODE asc";
		return DataUtil.getPageSelectJson(sql, null);
	}
	
	
	/**
	 * 新增监测项目信息 
	 * 张文成
	 * 2016年12月10日
	 * @param res
	 */
	public boolean createMonitorParam(Map<String, String> parm,Map<String,String> oom) throws Exception{
		try{    
				DataUtil.beginTransaction();
				String GUID =  UUID.randomUUID().toString();
				String sql=" insert into QC_MONITORPARAM(GUID,MONITORTYPECODE,MONITORPARAMNAME,MONITORPARAMCODE,MONITORPARAMUNIT,ISFIELDPARAM,RDCM) "
							+ " values('"+GUID+"','"+parm.get("MONITORTYPECODE")+"','"+parm.get("MONITORPARAMNAME")+"'"
									+ ",'"+parm.get("MONITORPARAMCODE")+"','"+parm.get("MONITORPARAMUNIT")+"','"+parm.get("ISFIELDPARAM")+"'";
				if(StringUtil.IsNotNullOrEmpty(parm.get("RDCM"))){
					sql+=" ,'"+parm.get("RDCM")+"')";
				}else{
					sql+=" ,null)";
				}
				DataUtil.ExecuteUpdateSql(sql, null);	
				
				//数量级数据入库
				sql = "delete QC_MONITORPARAMDETAIL t where t.PARAMGUID = '"+GUID+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				int countOOM = Integer.parseInt(oom.get("OOMLENGTH").trim());
				for(int i=0;i<countOOM;i++)
				{  
					String OOMGUID =  UUID.randomUUID().toString();
					sql =" insert into QC_MONITORPARAMDETAIL(GUID,PARAMGUID,MCUPLIMIT,MCDOWNLIMIT,PMAD,SRRUPLIMIT,SRRDOWNLIMIT,REOSS)"
							+ " values('"+OOMGUID+"','"+GUID+"'";
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][MCUPLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][MCUPLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][MCDOWNLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][MCDOWNLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][PMAD]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][PMAD]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][SRRUPLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][SRRUPLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][SRRDOWNLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][SRRDOWNLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][REOSS]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][REOSS]")+"')";
					}else{
						sql+=" ,null)";
					}
					DataUtil.ExecuteUpdateSql(sql, null);	
				}
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 编辑监测项目信息 
	 * 张文成
	 * 2016年12月10日
	 * @param res
	 */
	public boolean editMonitorParam(Map<String, String> parm,Map<String,String> oom) throws Exception{
		try{
				DataUtil.beginTransaction();				
				String sql=" update QC_MONITORPARAM set MONITORTYPECODE='"+parm.get("MONITORTYPECODE")+"'"
						+ " ,MONITORPARAMNAME='"+parm.get("MONITORPARAMNAME")+"',MONITORPARAMCODE='"+parm.get("MONITORPARAMCODE")+"' "
						+ " ,MONITORPARAMUNIT='"+parm.get("MONITORPARAMUNIT")+"',ISFIELDPARAM='"+parm.get("ISFIELDPARAM")+"'";
				if(StringUtil.IsNotNullOrEmpty(parm.get("RDCM"))){
					sql+=" ,RDCM = '"+parm.get("RDCM")+"'";
				}else{
					sql+=" ,RDCM = null";
				}
				sql+=" where GUID='"+parm.get("GUID")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				
				sql = "delete QC_MONITORPARAMDETAIL t where t.PARAMGUID = '"+parm.get("GUID")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				//数量级数据编辑
				int countOOM = Integer.parseInt(oom.get("OOMLENGTH").trim());
				for(int i=0;i<countOOM;i++)
				{  
					String OOMGUID =  UUID.randomUUID().toString();
					sql =" insert into QC_MONITORPARAMDETAIL(GUID,PARAMGUID,MCUPLIMIT,MCDOWNLIMIT,PMAD,SRRUPLIMIT,SRRDOWNLIMIT,REOSS)"
							+ " values('"+OOMGUID+"','"+parm.get("GUID")+"'";
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][MCUPLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][MCUPLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][MCDOWNLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][MCDOWNLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][PMAD]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][PMAD]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][SRRUPLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][SRRUPLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][SRRDOWNLIMIT]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][SRRDOWNLIMIT]")+"'";
					}else{
						sql+=" ,null";
					}
					if(StringUtil.IsNotNullOrEmpty(oom.get("OOM["+i+"][REOSS]"))){
						sql+=" ,'"+oom.get("OOM["+i+"][REOSS]")+"')";
					}else{
						sql+=" ,null)";
					}
					DataUtil.ExecuteUpdateSql(sql, null);	
				}

				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 删除监测项目信息 
	 * 张文成
	 * 2016年12月10日
	 * @param res
	 */
	public boolean deleteMonitorParam(Map<String, String> parm) throws Exception{
		try{
				DataUtil.beginTransaction();
				String sql=" delete QC_MONITORPARAM where GUID = '"+parm.get("GUID")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				sql=" delete QC_MONITORPARAMDETAIL where PARAMGUID = '"+parm.get("GUID")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 获取监测项目信息 
	 * 张文成
	 * 2016年12月10日
	 * @param res
	 * @throws Exception 
	 */
	public String getMonitorParam(Map<String, String> parm) throws Exception {
			String sql="select t.*,t2.s_domaincaption as MONITORTYPENAME from qc_monitorparam t"
					+ " left join DATA_DEFULATDOMAIN t2 on t.monitortypecode = t2.s_domaincode" 
					+ " where t2.s_domainname = '质控监测类型'";
				if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORTYPECODE"))){
					sql+=" and t.MONITORTYPECODE = '"+parm.get("MONITORTYPECODE")+"'";
				}		
				sql+= " order by t.monitortypecode,t.monitorparamname asc";
			return DataUtil.getPageSelectJson(sql, null);
	}
	
	/**
	 * 获取监测项目信息 
	 * 张文成
	 * 2016年12月10日
	 * @param res
	 * @throws Exception 
	 */
	public String getMonitorParamData(String[] monitorTypeCode) throws Exception {
		String sql="select t.*,t2.s_domaincaption as MONITORTYPENAME,t2.s_domaincaption||'-'||t.monitorparamname as NAME from qc_monitorparam t"
				+ " left join DATA_DEFULATDOMAIN t2 on t.monitortypecode = t2.s_domaincode" 
				+ " where t2.s_domainname = '质控监测类型'";	
			sql+=" and t.MONITORTYPECODE in ";
			ParameterSqlModel parmSqlModel = SqlUtil.prepareArrayParmAndSql(monitorTypeCode, "monitorTypeCode");
			sql += parmSqlModel.getSql();
			List<GlobalParameter> pc = new ArrayList<>();
			pc.add(new GlobalParameter("monitorTypeCode", monitorTypeCode));
			sql+= " order by t.monitortypecode,t.monitorparamname asc";
		List<RowBase> result = DataUtil.getSelectRows(sql, parmSqlModel.getParms());
		JSONArray json = JSONArray.fromObject(result);
		return json.toString();
	}
	
	/**
	 * 获取监测项目详情信息（数量级） 
	 * 张文成
	 * 2017年2月7日
	 * @param res
	 * @throws Exception 
	 */
	public String getMonitorParamDetail(Map<String, String> parm) throws Exception {
			String sql="select t.* from qc_monitorparamdetail t where t.paramguid = '"+parm.get("GUID")+"'";	
			return DataUtil.getPageSelectJson(sql, null);
	}

	
	/**
	 * 根据监测类型获取去重的监测项目信息 (监测类型与监测项目直接对应)
	 * 张文成
	 * 2016年12月16日
	 * @param res
	 * @throws Exception 
	 */
	public String getMonitorParamByMonitorType(Map<String, String> parm) throws Exception {	
		String sql="select distinct t.monitorparamcode,t.monitorparamname from qc_monitorparam t where 1=1 ";
		if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORTYPECODE"))){
			sql+=" and t.MONITORTYPECODE in("+ParamPartition(parm.get("MONITORTYPECODE"))+")";
		}		
		sql+= " order by t.monitorparamname asc";
		return DataUtil.getPageSelectJson(sql, null);
		
//			String sql="select distinct t.monitorparamcode,t.monitorparamname from qc_monitorparam t where 1=1 ";
//			if(StringUtil.IsNotNullOrEmpty(parm.get("MONITORTYPECODE"))){
//				sql+=" and t.MONITORTYPECODE in("+ParamPartition(parm.get("MONITORTYPECODE"))+")";
//			}		
//			sql+= " order by t.monitorparamname asc";
//			List<RowBase> result = DataUtil.getSelectRows(sql, null);
//			JSONArray json = JSONArray.fromObject(result);
//			return json.toString();
	}
	
	/**
	 * 新增分析方法信息 
	 * 张文成
	 * 2016年12月15日
	 * @param res
	 */
	public boolean createExpMethod(Map<String, String> parm) throws Exception{
		try{    
				DataUtil.beginTransaction();
				String sql=" insert into QC_EXPMETHOD(METHODCODE,COMPAYDETECTION,MONITORPARAMCODE,SMAN,METHODNAME,MINDETECTIONLIMIT,MINDETECTIONLIMITUNIT,MONITORTYPECODE) "
							+ " values(sys_guid(),'"+parm.get("COMPAYDETECTION")+"','"+parm.get("MONITORPARAMCODE")+"','"+parm.get("SMAN")+"','"+parm.get("METHODNAME")+"'"
									+ ",'"+parm.get("MINDETECTIONLIMIT")+"','"+parm.get("MINDETECTIONLIMITUNIT")+"','"+parm.get("MONITORTYPECODE")+"')";
				DataUtil.ExecuteUpdateSql(sql, null);	
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				e.printStackTrace();
				DataUtil.rollbackTransaction();
				return false;
			}
	}
	/**
	 * 编辑分析方法信息 
	 * 张文成
	 * 2016年12月15日
	 * @param res
	 */
	public boolean editExpMethod(Map<String, String> parm) throws Exception{
		try{
				DataUtil.beginTransaction();				
				String sql=" update QC_EXPMETHOD set MONITORPARAMCODE='"+parm.get("MONITORPARAMCODE")+"',SMAN='"+parm.get("SMAN")+"'"
						+ " ,METHODNAME='"+parm.get("METHODNAME")+"',MINDETECTIONLIMIT='"+parm.get("MINDETECTIONLIMIT")+"' "
						+ " ,COMPAYDETECTION='"+parm.get("COMPAYDETECTION")+"',MINDETECTIONLIMITUNIT='"+parm.get("MINDETECTIONLIMITUNIT")+"',MONITORTYPECODE='"+parm.get("MONITORTYPECODE")+"'"
						+ " where METHODCODE='"+parm.get("METHODCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				DataUtil.commitTransaction();	
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return true;
			}
	}
	/**
	 * 删除分析方法信息 
	 * 张文成
	 * 2016年12月10日
	 * @param res
	 */
	public boolean deleteExpMethod(Map<String, String> parm) throws Exception{
		try{
				DataUtil.beginTransaction();
				String sql=" delete QC_EXPMETHOD where METHODCODE = '"+parm.get("METHODCODE")+"'";
				DataUtil.ExecuteUpdateSql(sql, null);
				DataUtil.commitTransaction();
				return true;
			} catch (Exception e) {
				DataUtil.rollbackTransaction();
				return true;
			}
	}
	/**
	 * 获取分析方法信息 
	 * 张文成
	 * 2016年12月10日
	 * @param res
	 * @throws Exception 
	 */
	public String getExpMethod() throws Exception {
			String sql="select t.*,t2.s_domaincaption as MONITORTYPENAME,t1.monitorparamname as MONITORPARAMNAME from qc_expmethod t"
					+" left join (select distinct monitorparamcode,monitorparamname from qc_monitorparam) t1 on t.monitorparamcode = t1.monitorparamcode left join DATA_DEFULATDOMAIN t2 on t.monitortypecode = t2.s_domaincode"
					+" where t2.s_domainname = '质控监测类型'";
				sql+= " order by t.monitortypecode,t1.monitorparamname asc";
			return DataUtil.getPageSelectJson(sql, null);
	}
	
	/**
	 * 查询质控站点坐标数据
	 * @throws Exception 
	 */
	public List<RowBase> queryQCStationWithSpatial(Map<String, String> params) throws Exception {
		String sql = "select t.pointcode AS POINTCODE,t1.photo,t.companycode,t1.temp_photo,t.pointtype as POINTTYPECODE,t2.companyname AS COMPANYNAME ,t1.name as POINTNAME,t1.sample_position as SAMPLEPOSITION,"
				+ " t3.s_domaincaption AS POINTTYPE ,t1.X as LONGITUDE,t1.Y as LATITUDE,'POINT('||t1.X||' '||t1.Y||')' as wkt"
				+ " from qc_pointinfo t left join BC_SITE_INFO t1 on t.pointcode = t1.code left join qc_companyinfo t2 on t.companycode = t2.companycode"
				+ " left join DATA_DEFULATDOMAIN t3 on t.pointtype  = t3.s_domaincode where t3.s_domainname = '质控监测类型'";
		if(StringUtil.IsNotNullOrEmpty(params.get("POINTTYPECODE"))){
			sql+=" and t.pointtype = '"+params.get("POINTTYPECODE")+"'";
		}		
		if(StringUtil.IsNotNullOrEmpty(params.get("POINTNAME"))){
			sql+=" and t1.name like '%"+params.get("POINTNAME")+"%'";
		}	
		if(StringUtil.IsNotNullOrEmpty(params.get("CompanyCode"))){
			sql+=" and t2.companycode in("+ParamPartition(params.get("CompanyCode"))+")";
		}
		sql+=" order by t2.companyname,t1.sample_position";
		List<RowBase> resultRows = DataUtil.getSelectRows(sql, null);
		resultRows = DataHelper.RowMerge(resultRows, new ArrayList<String>(){{add("COMPANYNAME");}});
		return resultRows;
	}
	/**
	 * 根据公司、监测类型获取参数
	 * @param param
	 * @return
	 */
	public List<RowBase> getCompanyParam(Map<String,String> param){
		String sql="select t1.monitorparamcode,t1.monitorparamname from qc_companyparaminfo t"
				 +" left join qc_monitorparam t1 on t1.guid=t.paramguid   where t1.isfieldparam='1'";
		if(StringUtil.IsNotNullOrEmpty(param.get("CompanyCode"))){
			sql+=" and  t.companycode='"+param.get("CompanyCode")+"'";
		}
		if(StringUtil.IsNotNullOrEmpty(param.get("QCType"))){
			sql+=" and t1.monitortypecode='"+param.get("QCType")+"'";
		}
		return DataUtil.getSelectRows(sql, null);
	}
	/**
	 * 获取站点参数
	 * @param param
	 * @return
	 */
	public List<RowBase> getStationParam(Map<String,String> param){
		String sql="select t.paramcode from QC_MONITORSTATIONPARAM  t where 1=1";
		if(StringUtil.IsNotNullOrEmpty(param.get("POINTCODE"))){
			sql+=" and t.stationcode='"+param.get("POINTCODE")+"'";
		}
		return DataUtil.getSelectRows(sql, null);
	}
	/**
	 * 通过站点编号删除关联参数
	 * @param param
	 */
	public void delStationParam(Map<String,String> param){
		if(StringUtil.IsNotNullOrEmpty(param.get("POINTCODE"))){
			String sql="delete QC_MONITORSTATIONPARAM t where t.stationcode='"+param.get("POINTCODE")+"'";
			DataUtil.ExecuteUpdateSql(sql, null);
		}
	}
	/**
	 * 根据用户角色获取公司信息
	 * @param param
	 * @return
	 */
	public List<RowBase> getRoleCompany(Map<String,String> param){
		String sql=" select t.s_userid,wmsys.wm_concat(distinct t2.station_code) as code from safe_userrole t "
				  +" left join  OC_ROLE_SITE_PRIVILEGE t2 on t.s_roleid=t2.role_id"
				  +" left join qc_companyinfo t3 on t3.companycode=t2.station_code"
				  +" where t3.companyname is not null and";
		if(StringUtil.IsNotNullOrEmpty(param.get("USERID"))){
			sql+=" t.s_userid='"+param.get("USERID")+"'";
		}
		sql+="  group by t.s_userid";
		return DataUtil.getSelectRows(sql, null);
	}
	public String ParamPartition(String paramData){
		String[] param=paramData.split(",");
		String StrParam="";
		for(int i=0;i<param.length;i++){
			if(param.length-i==1){
				StrParam+="'"+param[i]+"'";
			}else{
				StrParam+="'"+param[i]+"',";
			}			
		}
		return StrParam;
	}
}
